-- adds a new Item, which name must be unique
-- returns true on success, false otherwise
DROP FUNCTION IF EXISTS add_item(VARCHAR(32), VARCHAR(32), VARCHAR(32));
CREATE OR REPLACE FUNCTION add_item(_name VARCHAR(32), _brand VARCHAR(32), _description VARCHAR(32))
RETURNS BOOLEAN AS
$$
DECLARE
   tmp_brand INT;

BEGIN
  SELECT id_brands INTO tmp_brand FROM BRANDS WHERE name = _brand;

  INSERT INTO ITEMS (fk_brand, name, description)
  VALUES (tmp_brand, _name, _description);

  RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
  RAISE WARNING '% - %', SQLERRM, SQLSTATE;
  RETURN FALSE;
END;

$$ LANGUAGE plpgsql;


-- adds a new character which name must be unique
-- Returns false if
--   it's a duplicate (this name is already use)
--   voice is not in [0;MAX_VOICES]
--   skin_color is not in [0;MAX_COLORS_SKINS]
--   id length is not in [3;8]

-- TODO build measurements (binary) from names / values
-- TODO (int)female == voice % 2
-- TODO name: unique quote && espace
-- TODO blacklist management
DROP FUNCTION IF EXISTS
  add_character(VARCHAR(32), VARCHAR(6), BYTEA, BOOLEAN, BOOLEAN, VARCHAR(16), VARCHAR(16), VARCHAR(16));
CREATE OR REPLACE FUNCTION
  add_character(_name VARCHAR(32), _id VARCHAR(6), _measurements BYTEA,
                _female BOOLEAN, _left_handed BOOLEAN,
                -- Foreign Keys Management
                _language VARCHAR(16), _skin_color VARCHAR(16), _voice VARCHAR(16))
RETURNS BOOLEAN AS
$$
DECLARE
   tmp_language INT;
   tmp_skin INT;
   tmp_voice INT;

BEGIN
  SELECT id_languages INTO tmp_language FROM LANGUAGES WHERE name = _language;
  SELECT id_skin_colors INTO tmp_skin FROM SKIN_COLORS WHERE name = _skin_color;
  SELECT id_voices INTO tmp_voice FROM VOICES WHERE name = _voice;

  INSERT INTO CHARACTERS (name, id, measurements, female, left_handed, fk_language, fk_skin_color, fk_voice)
  VALUES (_name, _id, _measurements, _female, _left_handed, tmp_language, tmp_skin, tmp_voice);

  RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
  RAISE WARNING '% - %', SQLERRM, SQLSTATE;
  RETURN FALSE;
END;

$$ LANGUAGE plpgsql;



-- adds a new barrel which name must be unique
-- Returns false if it's a duplicate (this name is already use)
DROP FUNCTION IF EXISTS add_barrel(VARCHAR(32), VARCHAR(32), INT, FLOAT);
CREATE OR REPLACE FUNCTION add_barrel(_brand_name VARCHAR(32), _name VARCHAR(32), _length INT, _caliber FLOAT)
RETURNS BOOLEAN AS
$$
DECLARE
  tmp_brand INT;
  tmp_item INT;
  barrel_id INT;
  tmp_serial INT;
  material_id INT;
BEGIN
  SELECT id_itemtypes INTO barrel_id FROM ITEMTYPES WHERE name = 'Barrel';
  SELECT id_brands INTO tmp_brand FROM BRANDS WHERE name = _brand_name;
  SELECT id_materials INTO material_id FROM MATERIALS WHERE name = 'Stainless Steel';

  INSERT INTO ITEMS (fk_material, fk_itemtypes, fk_brand, name, length)
  VALUES (material_id, barrel_id, tmp_brand, _name, _length);
  tmp_serial = currval('items_id_items_seq');

  INSERT INTO BARRELS (fk_item, caliber)
  VALUES (tmp_serial, _caliber);

  RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
  RAISE WARNING '% - %', SQLERRM, SQLSTATE;
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


-- adds a new (meaning its durability is 100%) Gun, which name must be unique
-- Returns false if it's a duplicate (this name is already use)
DROP FUNCTION IF EXISTS add_gun(VARCHAR(16), VARCHAR(32), VARCHAR(32), INT, INT, VARCHAR(32), VARCHAR(32), MONEY);
CREATE OR REPLACE FUNCTION add_gun(_type VARCHAR(16), _brand VARCHAR(32), _name VARCHAR(32), _length INT, _weight INT, _material VARCHAR(32), _finish VARCHAR(32), _price MONEY)
RETURNS BOOLEAN AS
$$
DECLARE
  tmp_type INT;
  tmp_brand INT;
  tmp_serial INT; -- Item id
  tmp_material INT;

BEGIN
  SELECT id_brands INTO tmp_brand FROM BRANDS WHERE name = _brand;

  IF (tmp_brand IS NULL) THEN
    RAISE WARNING 'add_gun: brand *NOT* found: %', _brand;
    -- RETURN FALSE;

	-- brand auto-add
    INSERT INTO BRANDS (name)
  	VALUES (_brand);
    RAISE WARNING 'added % brand', _brand;
    SELECT id_brands INTO tmp_brand FROM BRANDS WHERE name = _brand;
  END IF;

  SELECT id_guntypes INTO tmp_type FROM GUNTYPES WHERE name = _type;
  IF (tmp_type IS NULL) THEN
    RAISE WARNING 'add_gun: type *NOT* found: %', _brand;
    RETURN FALSE;
  END IF;

  SELECT id_materials INTO tmp_material FROM MATERIALS WHERE name = _material;
  IF (tmp_material IS NULL) THEN
    RAISE WARNING 'add_gun: material *NOT* found: %', _material;
    RETURN FALSE;
  END IF;


  INSERT INTO ITEMS (fk_material, fk_brand, name, length, weight, price)
  VALUES (tmp_material, tmp_brand, _name, _length, _weight, _price);

  tmp_serial = currval('items_id_items_seq');

  INSERT INTO GUNFRAMES (fk_item, fk_guntype, durability)
  VALUES (tmp_serial, tmp_type, 100);

  RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
  RAISE WARNING '% - %', SQLERRM, SQLSTATE;
  RETURN FALSE;
END;

$$ LANGUAGE plpgsql;
